﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using SIS.DataAccess.Ultilities;
namespace SIS.DataAccess
{
	/// <summary>
	/// Author: hieunv
	/// </summary>
	[Serializable]
	public class Student : System.Data.DataTable
	{
		public const string STUDENT_ID = "ID";
		public const string STUDENT_NAME = "name";
		public const string DEPARTMENT_NAME = "dept_name";
		public const string TOTAL_CREDIT = "tot_cred";

		private static Student _instance;

		public static Student Instance
		{
			get
			{
				if (_instance == null)
				{
					_instance = new Student();
					Student.Fill();
				}
				return _instance;
			}
		}
		public Student()
			: base("Student")
		{
			InitComponents();
		}

		public Student(SerializationInfo info, StreamingContext context)
			: base(info, context)
		{
			InitComponents();
		}
		
		private void InitComponents()
		{
			var id = new System.Data.DataColumn(STUDENT_ID, typeof(string));
			id.AllowDBNull = false;
			id.Unique = true;

			var name = new System.Data.DataColumn(STUDENT_NAME, typeof(string));
			name.AllowDBNull = false;

			var departmentName = new System.Data.DataColumn(DEPARTMENT_NAME, typeof(string));
			departmentName.AllowDBNull = true;

			var totalCredit = new System.Data.DataColumn(TOTAL_CREDIT, typeof(int));
			totalCredit.AllowDBNull = true;

			this.Columns.Add(id);
			this.Columns.Add(name);
			this.Columns.Add(departmentName);
			this.Columns.Add(totalCredit);

			this.PrimaryKey = new System.Data.DataColumn[] { id };
		}

		protected override Type GetRowType()
		{
			return typeof(SIS.DataAccess.Entities.Student);
		}

		protected override DataRow NewRowFromBuilder(DataRowBuilder builder)
		{
			return (new SIS.DataAccess.Entities.Student(builder) as DataRow);
		}

		public bool Insert()
		{
			try
			{
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@p1", SqlDbType.VarChar, 5, STUDENT_ID));
				parameters.Add(new SqlParameter("@p2", SqlDbType.VarChar, 20, STUDENT_NAME));
				parameters.Add(new SqlParameter("@p3", SqlDbType.VarChar, 20, DEPARTMENT_NAME));
				parameters.Add(new SqlParameter("@p4", SqlDbType.Int, 3, DEPARTMENT_NAME));
				string query = string.Format("INSERT INTO student({0}, {1}, {2}, {3}) VALUES(@p1, @p2, @p3, @p4)", STUDENT_ID, STUDENT_NAME, DEPARTMENT_NAME, TOTAL_CREDIT);
				return (DataServices.ExecuteQuery(QueryType.INSERT, CommandType.Text, query, parameters, this) > 0);
			}
			catch (System.Exception exc)
			{
				this.RejectChanges();
				System.Diagnostics.Debug.WriteLine(exc.StackTrace);
				throw exc;
			}
		}
		public bool Update()
		{
			try
			{
				string query = string.Format("UPDATE student SET {0}=@a0, {1}=@a1, {2}=@a2 WHERE {4}=@p0", STUDENT_NAME, DEPARTMENT_NAME, TOTAL_CREDIT, STUDENT_ID);
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@a0", SqlDbType.VarChar, 20, STUDENT_NAME));
				parameters.Add(new SqlParameter("@a1", SqlDbType.VarChar, 20, DEPARTMENT_NAME));
				parameters.Add(new SqlParameter("@a2", SqlDbType.VarChar, 3, TOTAL_CREDIT));				
				var p0 = new SqlParameter("@p1", SqlDbType.VarChar, 8, STUDENT_ID);
				p0.SourceVersion = DataRowVersion.Original;
				parameters.Add(p0);				
				return (DataServices.ExecuteQuery(QueryType.UPDATE, CommandType.Text, query, parameters, this) > 0);
			}
			catch (Exception exc)
			{
				this.RejectChanges();
				System.Diagnostics.Debug.WriteLine(exc.StackTrace);
				throw exc;
			}
		}
		public bool Delete()
		{
			try
			{
				String query = String.Format("DELETE FROM student WHERE {0}=@p0", STUDENT_ID);
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@p0", SqlDbType.VarChar, 5, STUDENT_ID));
				return (DataServices.ExecuteQuery(QueryType.DELETE, CommandType.Text, query, parameters, this) > 0);				
			}
			catch (Exception exc)
			{
				this.RejectChanges();
				System.Diagnostics.Debug.WriteLine(exc.StackTrace);
				throw exc;
			}
		}

		public static void Fill()
		{
			DataAccess.Ultilities.DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT * FROM student", null, Student.Instance);
		}

		public static Entities.Student GetStudent(string studentID)
		{
			//string query = "SELECT * FROM student WHERE ID=@p1";
			//List<SqlParameter> parameters = new List<SqlParameter>();
			//SqlParameter p1 = new SqlParameter("@p1", SqlDbType.VarChar, 5, STUDENT_ID);
			//p1.Value = id;
			//parameters.Add(p1);
			//Student students = new Student();
			//if (DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, query, parameters, students) > 0)
			//{
			//    return students.Rows[0] as SIS.DataAccess.Entities.Student;
			//}
			//else return null;
			return Instance.Select(string.Format("id='{0}'", studentID))[0] as SIS.DataAccess.Entities.Student;
		}
	}
}
